HW#5 --- last modified January 27 2019 04:58:09..

Solution set.

Due date: May 14

Files to be submitted:
  Hw5.zip

Purpose: To gain experience with information integration, data mining, and OLAP.

Related Course Outcomes:

The main course outcomes covered by this assignment are:

CLO7 -- Be able to create or deploy a query mediator for a system with at least two data sources.

CLO8 -- Be able to determine how the A Priori algorithm would operate on a toy dataset

CLO9 -- Design and deploy analytical databases for OLAP.

Specification:

This homework will consist of two parts, a written part and a coding/experiment part. Both parts will be submitted in the Hw5.zip file. The written part should be in a file, named Hw5.pdf, within this zip-file. For the written part of the homework you should write solutions for the book questions listed below. In what you turn in, make sure to write the names and student ids for each group member. For each problem, first copy and paste the question, then write your solution to it beneath it.

• Exercise 20.5.2a, 20.6.2, 22.1.1, 22.2.1

For the coding part of the homework, I want you to write a simulator for the following real world set-up: You have a mobile fitness app which stores data locally in a sqlite database. The app developer has their own site where they keep an OLAP database using Mysql of what exercises people are doing, when during the day, and in what geographic region. The mobile app phones home periodical with the changes since the last time in spoke with the developers website. This is essentially a mediated transaction the mobile database and the OLAP database.

Although you could actually code a mobile app for this homework in Java using Android, that would be way too much work. Instead, I want you to write two command line programs to simulate our set-up: MobileApp.java and Olap.java. For these programs, the top of your program's class file should have any constants the grader needs to tweak to make a JDBC connection to a Mysql DB or sqlite Db. You should have a README.txt file saying what changes the grader needs to make to get your code to work on the grader's machine. You should also have a file olap.sql, the grader can use to create your Mysql database. You can assume the grader has Java 9 SE installed and will compile your programs from the command line with the lines:

javac MobileApp.java
javac Olap.java

MobileApp should support three different functionalities, each of which can be tested from the command line, using lines like:

java MobileApp workout id location type start_time duration
java MobileApp show id date format
java MobileApp send id

Here id should be some 9 digit device id such as 123456789. The workout command should store in a sqlite file id.sqlite (where id has been replaced with a number) in a table WORK_OUT, the information location, type, start_time, duration. Here location consists of two int's separated by a hyphen, the first being a latitude, the second a longitude. For example, 37-121. The type should be one of: walk, run, swim, bike. The start_time should be in the format: YYYY-mm-dd:HH:ii:ss. For example, 2018-05-01:23:05:35. Notice hours are represented using a 24 hour clock, and leading zeros are used if the value is below 10. The duration should be in the format: HH:ii:ss. An example using this command might be:

java MobileApp workout 987654321 45-100 swim 2018-05-01:23:05:35 01:00:05

The second command simulates a user using their fitness tracking MobileApp to look at workout that have already done. In java MobileApp show id date format, id is as in the workout command, date should be in the format YYYY-mm-dd, and format should be one of duration or location. For example,

java MobileApp show 123456789 2018-04-29 duration

This should for each time of exercise list out the amount of time spent on that exercise for that day. For example,

walk 02:15
run 00:10
swim 00:00
bike 00:30

If location had been chosen then the amount of time spent on that exercise should be grouped by location. For example, the output might look like:

37-121 01:45
37-122 00:15

The id.sqlite files should have a table that used to keep track of the last time that data was sent to the app developer's servers. The command java MobileApp send id should use this last time info and read the data in the WORK_OUT table since that time, and write it into a Mysql database named olap. It should then update the last time data sent info in the id.sqlite database.

Your Olap program should be run from the command line with a command like:

java olap region type time_period

It should then output the average daily work out duration for that region and time period. Further it should also output the rollup (your query should have a WITH ROLLUP as part of its GROUP UP) of these value. Here region is of the form lat1-long1:lat2-long2 representing a rectangle and time period can be YYYY or YYYY-mm, or YYYY-mm-dd. For example,

java olap 35-115:45-125 swim 2018-05

Might use a where clause for the region, but do a group by with roll up on the remaining parameters to output:

type year month avg
swim 2018   5   1.5
swim 2018  NULL 1.2
swim NULL  NULL 1.3
NULL NULL  NULL 0.9

This completes the description of what your programs need to do for this homework, below is the grading scheme.

Point Breakdown